if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_schedule_update_by_date')
          and type in ('P','PC'))
   drop procedure bd_proc_schedule_update_by_date
go


create procedure bd_proc_schedule_update_by_date (@user_serial_str varchar(max),@year_month_day varchar(50),@ban_ci varchar(50),@ip varchar(50),@operator varchar(50)) as
BEGIN

-- 关闭打印受影响行数
SET NOCOUNT ON;


-- 定义返回受影响行数结果
	declare @result int = 0;


	--  异常扑捉机制
		BEGIN TRY


			create table #user_serial(
				id int identity,
				user_no varchar(50) COLLATE Chinese_PRC_CI_AS,
				user_name varchar(50) COLLATE Chinese_PRC_CI_AS,
				user_serial varchar(50) COLLATE Chinese_PRC_CI_AS,
				dept_name varchar(50)	COLLATE Chinese_PRC_CI_AS,
				dept_serial varchar(50) COLLATE Chinese_PRC_CI_AS
			)

      --创建 返回不满足排班的人的原因
      create table #return_table(
        user_serial VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
        user_no VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
        user_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
        dept_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
        error_msg VARCHAR(200) COLLATE Chinese_PRC_CI_AS
      )

			create table #shift_shid_info(
				id int identity,
				shift_id int,
				shift_name varchar(200)	COLLATE Chinese_PRC_CI_AS,
				shift_date date,
				shift_begin int,
				shift_end int,
				shift_sign_begin int,
				shift_sign_end int,
				shift_late int,
				shift_early int,
				shift_duty_on_delay int,
				shift_duty_off_delay int,
				shift_workday int,
				shift_workday_hours int,
				is_enjoyment_subsidized int,
				middle_shift_start_time int,
				middle_shift_end_time int,
				night_shift_start_time int,
				night_shift_end_time int,
				repast_start_time int,
				repast_end_time int,
				repast_start_time2 int,
				repast_end_time2 int,
				shift_is_rest int
			)

			create table #data(
				id int identity,
				user_no varchar(50)	COLLATE Chinese_PRC_CI_AS,
				user_name varchar(50)	COLLATE Chinese_PRC_CI_AS,
				user_serial varchar(50) COLLATE Chinese_PRC_CI_AS,
				dept_name varchar(50)	COLLATE Chinese_PRC_CI_AS,
				dept_serial varchar(50) COLLATE Chinese_PRC_CI_AS,
				shift_id int,
				shift_name varchar(200)	COLLATE Chinese_PRC_CI_AS,
				shift_date date,
				shift_begin int,
				shift_end int,
				shift_sign_begin int,
				shift_sign_end int,
				shift_late int,
				shift_early int,
				shift_duty_on_delay int,
				shift_duty_off_delay int,
				shift_workday int,
				shift_workday_hours int,
				is_enjoyment_subsidized int,
				middle_shift_start_time int,
				middle_shift_end_time int,
				night_shift_start_time int,
				night_shift_end_time int,
				repast_start_time int,
				repast_end_time int,
				repast_start_time2 int,
				repast_end_time2 int,
				shift_is_rest int,
				is_have_ot int null,
				is_created_ot int null
			)

		-- 开启事务
			BEGIN TRAN

				-- 拆分用户编号
				insert into #user_serial(user_serial)
				select * from string_split(@user_serial_str,',');


				-- 更新用户工号、名称、部门编号
				update a set a.user_no = isnull(b.user_no,''), a.user_name = isnull(b.user_lname,''), a.dept_serial = isnull(b.user_dep,'')
				from #user_serial a
				inner join dt_user b on a.user_serial = b.user_serial

        -- 更新部门名称
        update a set a.dept_name = isnull(b.dep_name,'')
        from #user_serial a
        inner join dt_dep b on a.dept_serial = b.dep_serial

				-- 查询班次保存临时表中(这里普通班次默认都是工作日)
				declare @ban_ci_name varchar(50);
				select @ban_ci_name = concat('[A]',isnull(bh,''),'|',isnull(mc,'')) FROM zt_banci t WHERE bh = @ban_ci

				-- 获取排班信息
				insert into #shift_shid_info(shift_id,shift_name,shift_date,shift_begin,shift_end,shift_sign_begin,shift_sign_end,shift_late,shift_early,shift_duty_on_delay,shift_duty_off_delay,shift_workday,shift_workday_hours,is_enjoyment_subsidized,middle_shift_start_time,middle_shift_end_time,night_shift_start_time,night_shift_end_time,repast_start_time,repast_end_time,repast_start_time2,repast_end_time2)
				select a.id,@ban_ci_name,@year_month_day,isnull(b.sbsj,0),isnull(b.xbsj,0),isnull(b.sbks,0),isnull(b.xbjs,0),isnull(b.cdsj,0),isnull(b.ztsj,0),isnull(b.bjcd,0),isnull(b.bjzt,0),isnull(b.alld,0),isnull(b.allt,0),isnull(b.is_enjoyment_subsidized,0),isnull(b.middle_shift_start_time,0),isnull(b.middle_shift_end_time,0),isnull(b.night_shift_start_time,0),isnull(b.night_shift_end_time,0),isnull(repast_start_time,0),isnull(b.repast_end_time,0),isnull(repast_start_time2,0),isnull(b.repast_end_time2,0)
				from zt_banci a
				inner join zt_shid b on a.bh = b.parent
				where a.bh = @ban_ci and b.lx = 0	--上下班的班次信息


				-- 封装基础数据
				insert into #data(user_no,user_name,user_serial,dept_name,dept_serial,shift_id,shift_name,shift_date,shift_begin,shift_end,shift_sign_begin,shift_sign_end,shift_late,shift_early,shift_duty_on_delay,shift_duty_off_delay,shift_workday,shift_workday_hours,is_enjoyment_subsidized,middle_shift_start_time,middle_shift_end_time,night_shift_start_time,night_shift_end_time,repast_start_time,repast_end_time,repast_start_time2,repast_end_time2)
				select a.user_no,a.user_name,a.user_serial,a.dept_name,a.dept_serial,b.shift_id,b.shift_name,b.shift_date,isnull(b.shift_begin,0),isnull(b.shift_end,0),isnull(b.shift_sign_begin,0),isnull(b.shift_sign_end,0),isnull(b.shift_late,0),isnull(b.shift_early,0),isnull(b.shift_duty_on_delay,0),isnull(b.shift_duty_off_delay,0),isnull(b.shift_workday,0),isnull(b.shift_workday_hours,0),isnull(b.is_enjoyment_subsidized,0),isnull(b.middle_shift_start_time,0),isnull(b.middle_shift_end_time,0),isnull(b.night_shift_start_time,0),isnull(b.night_shift_end_time,0),isnull(repast_start_time,0),isnull(b.repast_end_time,0),isnull(repast_start_time2,0),isnull(b.repast_end_time2,0)
				from #user_serial a,#shift_shid_info b


				--declare @shift_is_rest int;
				--set @shift_is_rest = iif(left(isnull(@ban_ci_name,''),3) = '[X]',1,0)
        --update #data set shift_is_rest = iif(left(isnull(shift_name,''),3) = '[X]',1,0)


				--根据日历，将非工作日的符号改为[X]
				update a
					set a.shift_name = case when (d.is_weekend=1 or d.is_statutory=1) then concat('[X]',substring(a.shift_name,4,len(a.shift_name))) else  a.shift_name end,
							a.shift_is_rest = case when (d.is_weekend=1 or d.is_statutory=1) then 1 else  0 end
				from  #data a
				INNER JOIN dt_user u on a.user_serial = u.user_serial
				INNER JOIN bd_kq_work_calendar_detail d on d.parent_id = u.user_work_calendar and a.shift_date = d.work_date

				--校验
				-- 1检测是否为封账状态
				insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
				select distinct a.user_serial,a.user_no,a.user_name,a.dept_name,'该时段考勤存在已经封账,不能重新排班'
				from #data  a
				left join bd_kq_accounts b on a.user_serial = b.user_serial and year(a.shift_date) = b.year and month(a.shift_date) = b.month
				where isnull(b.lock_status,0)>0
				-- 删除已经封账的数据(以用户为单位)
				delete a
				from #data a
				inner join #return_table b on a.user_serial = b.user_serial

				--2检测有无加班单
				--2检测之前是否有排班并且排班有生成的加班单（若该加班单存在，则该人本次全部日期都不能排班）
				insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
        select distinct a.user_serial,a.user_no,a.user_name,a.dept_name,'该时段存在排班加班单，不能重新排班'
        from #data a
        inner join bd_kq_schedule_detail d on a.user_serial = d.user_serial and a.shift_date = d.shift_date and d.is_created_ot = 1

        -- 删除已经有加班单的人员数据
        delete a
        from #data a
        inner join #return_table b on a.user_serial = b.user_serial


				--处理	is_have_ot,is_created_ot
				--班次有加班时段，则is_have_ot = 1 ，is_created_ot=0
				--班次没有加班字段 则is_have_ot = 0 ，is_created_ot=0
				update s
					set s.is_have_ot = case when isnull(sd.bh,0)=0 then (case when (isnull(bc.id,0)!=0 and s.shift_is_rest=1) then 1 else 0 end) else 1 end ,
							s.is_created_ot = 0
				from #data s
				left join zt_banci bc on bc.id = s.shift_id
				left JOIN zt_shid sd ON bc.bh = sd.parent and sd.lx=1



				-- 有则更新 无则添加
				MERGE bd_kq_schedule_detail b
				USING #data a
				ON a.user_serial = b.user_serial and a.shift_date = b.shift_date
				WHEN MATCHED
					THEN
						UPDATE SET b.shift_id = a.shift_id,b.shift_name = a.shift_name,b.shift_begin = a.shift_begin,b.shift_end = a.shift_end,
						b.shift_sign_begin=a.shift_sign_begin,b.shift_sign_end=a.shift_sign_end,b.shift_late=a.shift_late,b.shift_early=a.shift_early,b.shift_duty_on_delay=a.shift_duty_on_delay,b.shift_duty_off_delay=a.shift_duty_off_delay,b.shift_workday=a.shift_workday,b.shift_workday_hours=a.shift_workday_hours,b.is_enjoyment_subsidized=a.is_enjoyment_subsidized,b.middle_shift_start_time=a.middle_shift_start_time,b.middle_shift_end_time=a.middle_shift_end_time,b.night_shift_start_time=a.night_shift_start_time,b.night_shift_end_time=a.night_shift_end_time,b.repast_start_time=a.repast_start_time,b.repast_end_time=a.repast_end_time,b.repast_start_time2=a.repast_start_time2,b.repast_end_time2=a.repast_end_time2,b.shift_is_rest = a.shift_is_rest,b.is_have_ot = a.is_have_ot,b.is_created_ot = a.is_created_ot,a.update_id= @operator,a.update_time = getdate()
				WHEN NOT MATCHED
					THEN
						insert (user_serial,shift_id,shift_name,shift_date,shift_begin,shift_end,shift_sign_begin,shift_sign_end,shift_late,shift_early,shift_duty_on_delay,shift_duty_off_delay,shift_workday,shift_workday_hours,is_enjoyment_subsidized,middle_shift_start_time,middle_shift_end_time,night_shift_start_time,night_shift_end_time,repast_start_time,repast_end_time,repast_start_time2,repast_end_time2,shift_is_rest,is_have_ot,is_created_ot,update_id,update_time)
						values (a.user_serial,a.shift_id,a.shift_name,a.shift_date,a.shift_begin,a.shift_end,a.shift_sign_begin,a.shift_sign_end,a.shift_late,a.shift_early,a.shift_duty_on_delay,a.shift_duty_off_delay,a.shift_workday,a.shift_workday_hours,a.is_enjoyment_subsidized,a.middle_shift_start_time,a.middle_shift_end_time,a.night_shift_start_time,a.night_shift_end_time,a.repast_start_time,a.repast_end_time,a.repast_start_time2,a.repast_end_time2,a.shift_is_rest,a.is_have_ot,a.is_created_ot,@operator,getdate());



				declare @day int = 0;
				set @day = day(@year_month_day);

				declare @sql varchar(max) = '';

				set @sql += 'update a set '+concat('d',@day)+' = b.shift_name ,gly_no = '''+@operator+''' ';
				set @sql += 'from kt_paiba a ';
				set @sql += 'inner join #data b on a.user_serial = b.user_serial ';
				set @sql += 'where a.rq = convert(varchar(7),'''+@year_month_day+''',120) ';

				exec(@sql);
				set @result = @result + @@ROWCOUNT;

				--验证放班
				--创建一个游标
			declare my_cursor cursor for select user_serial,shift_date from #data

			--变量
			declare @shift_date date;
			declare @user_serial varchar(200);
			--打开游标
			open my_cursor
			fetch next from my_cursor into @user_serial,@shift_date  --获取my_cursor的下一条数据，其中为两个字段分别赋值给@id,@name
			while @@FETCH_STATUS=0 begin --假如检索到了数据继续执行
				--1调用放班操作请假单存储过程
				--参数：@user_serial  ，@shift_date
				EXEC bd_proc_schedule_fb_leave @user_serial,@shift_date,@operator

				-- 2调用考勤计算(排班4)
				EXEC bd_proc_timecard_extend @shift_date,@shift_date,@user_serial,4

				fetch next from my_cursor into @user_serial,@shift_date --获取下一条数据并赋值给变量

			end--关闭释放游标
			close my_cursor
			deallocate my_cursor

		--  提交事务
			COMMIT TRAN

	--  结束异常捕捉
		END TRY

	--  异常处理
		BEGIN CATCH
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			END;
			RAISERROR (@errStr, 16, 1);
		END CATCH

  -- 返回错误信息
	select * from #return_table

--  清除临时表
	drop table #user_serial
	drop table #return_table



-- 打开打印受影响行数
SET NOCOUNT OFF;
END;
go
